import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  // 单例模式
  factory DatabaseHelper() => _instance;

  DatabaseHelper._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, 'mood_tracker.db');
    
    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDb,
    );
  }

  // 创建数据库表
  Future<void> _createDb(Database db, int version) async {
    // 情绪记录表
    await db.execute('''
      CREATE TABLE moods (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        mood_type INTEGER NOT NULL,
        mood_description TEXT,
        created_at TEXT NOT NULL
      )
    ''');

    // 日记表
    await db.execute('''
      CREATE TABLE diaries (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        content TEXT NOT NULL,
        mood_id INTEGER,
        created_at TEXT NOT NULL,
        FOREIGN KEY (mood_id) REFERENCES moods (id)
      )
    ''');

    // 情绪标签表
    await db.execute('''
      CREATE TABLE mood_tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        category TEXT NOT NULL
      )
    ''');

    // 日记-标签关联表
    await db.execute('''
      CREATE TABLE diary_tags (
        diary_id INTEGER,
        tag_id INTEGER,
        PRIMARY KEY (diary_id, tag_id),
        FOREIGN KEY (diary_id) REFERENCES diaries (id),
        FOREIGN KEY (tag_id) REFERENCES mood_tags (id)
      )
    ''');

    // 活动记录表
    await db.execute('''
      CREATE TABLE activities (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        activity_type TEXT NOT NULL,
        duration INTEGER,
        notes TEXT,
        created_at TEXT NOT NULL
      )
    ''');

    // 预设情绪标签
    await _insertDefaultMoodTags(db);
  }

  // 插入默认的情绪标签
  Future<void> _insertDefaultMoodTags(Database db) async {
    // 积极情绪标签
    final positiveTags = [
      {'name': '开心', 'category': '积极'},
      {'name': '放松', 'category': '积极'},
      {'name': '满足', 'category': '积极'},
      {'name': '感激', 'category': '积极'},
      {'name': '兴奋', 'category': '积极'},
      {'name': '平静', 'category': '积极'},
    ];

    // 消极情绪标签
    final negativeTags = [
      {'name': '焦虑', 'category': '消极'},
      {'name': '沮丧', 'category': '消极'},
      {'name': '生气', 'category': '消极'},
      {'name': '疲惫', 'category': '消极'},
      {'name': '压力', 'category': '消极'},
      {'name': '孤独', 'category': '消极'},
    ];

    // 中性情绪标签
    final neutralTags = [
      {'name': '平淡', 'category': '中性'},
      {'name': '思考', 'category': '中性'},
      {'name': '专注', 'category': '中性'},
      {'name': '好奇', 'category': '中性'},
    ];

    final allTags = [...positiveTags, ...negativeTags, ...neutralTags];
    
    for (var tag in allTags) {
      await db.insert('mood_tags', tag);
    }
  }

  // 插入情绪记录
  Future<int> insertMood(Map<String, dynamic> mood) async {
    final db = await database;
    return await db.insert('moods', mood);
  }

  // 插入日记
  Future<int> insertDiary(Map<String, dynamic> diary) async {
    final db = await database;
    return await db.insert('diaries', diary);
  }

  // 关联日记和标签
  Future<void> linkDiaryWithTags(int diaryId, List<int> tagIds) async {
    final db = await database;
    final batch = db.batch();
    
    for (var tagId in tagIds) {
      batch.insert('diary_tags', {
        'diary_id': diaryId,
        'tag_id': tagId,
      });
    }
    
    await batch.commit();
  }

  // 获取所有情绪标签
  Future<List<Map<String, dynamic>>> getMoodTags() async {
    final db = await database;
    return await db.query('mood_tags');
  }

  // 获取特定类别的情绪标签
  Future<List<Map<String, dynamic>>> getMoodTagsByCategory(String category) async {
    final db = await database;
    return await db.query(
      'mood_tags',
      where: 'category = ?',
      whereArgs: [category],
    );
  }

  // 获取特定日期的情绪记录
  Future<Map<String, dynamic>?> getMoodByDate(String date) async {
    final db = await database;
    final results = await db.query(
      'moods',
      where: 'date = ?',
      whereArgs: [date],
      limit: 1,
    );
    
    return results.isNotEmpty ? results.first : null;
  }

  // 获取特定日期的日记
  Future<Map<String, dynamic>?> getDiaryByDate(String date) async {
    final db = await database;
    final results = await db.query(
      'diaries',
      where: 'date = ?',
      whereArgs: [date],
      limit: 1,
    );
    
    return results.isNotEmpty ? results.first : null;
  }

  // 获取日记的标签
  Future<List<Map<String, dynamic>>> getTagsForDiary(int diaryId) async {
    final db = await database;
    return await db.rawQuery('''
      SELECT mt.* FROM mood_tags mt
      INNER JOIN diary_tags dt ON mt.id = dt.tag_id
      WHERE dt.diary_id = ?
    ''', [diaryId]);
  }

  // 获取所有日记，按日期排序
  Future<List<Map<String, dynamic>>> getAllDiaries() async {
    final db = await database;
    return await db.query(
      'diaries',
      orderBy: 'date DESC',
    );
  }

  // 获取日期范围内的情绪记录
  Future<List<Map<String, dynamic>>> getMoodsByDateRange(String startDate, String endDate) async {
    final db = await database;
    return await db.query(
      'moods',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [startDate, endDate],
      orderBy: 'date ASC',
    );
  }

  // 获取情绪统计数据
  Future<Map<int, int>> getMoodStats(String startDate, String endDate) async {
    final db = await database;
    final results = await db.rawQuery('''
      SELECT mood_type, COUNT(*) as count
      FROM moods
      WHERE date BETWEEN ? AND ?
      GROUP BY mood_type
    ''', [startDate, endDate]);
    
    final stats = <int, int>{};
    for (var result in results) {
      stats[result['mood_type'] as int] = result['count'] as int;
    }
    
    return stats;
  }

  // 插入活动记录
  Future<int> insertActivity(Map<String, dynamic> activity) async {
    final db = await database;
    return await db.insert('activities', activity);
  }

  // 获取日期范围内的活动记录
  Future<List<Map<String, dynamic>>> getActivitiesByDateRange(String startDate, String endDate) async {
    final db = await database;
    return await db.query(
      'activities',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [startDate, endDate],
      orderBy: 'date ASC',
    );
  }
} 